<?php
/*--------------------------------------------------------------
   remove_duplicated_newsletter_recipients.php 2022-12-16
   Gambio GmbH
   http://www.gambio.de
   Copyright (c) 2022 Gambio GmbH
   Released under the GNU General Public License (Version 2)
   [http://www.gnu.org/licenses/gpl-2.0.html]
 -------------------------------------------------------------*/

/** @var DatabaseModel $this */
/** @var int $t_success */

$duplicateRecipientsQuery = <<<SQL
    SELECT `mail_id`, `customers_email_address`
    FROM `newsletter_recipients`
    WHERE `customers_email_address` IN (
        SELECT `customers_email_address`
        FROM `newsletter_recipients`
        GROUP BY `customers_email_address`
        HAVING COUNT(`customers_email_address`) > 1
    )
    ORDER BY `mail_id`
SQL;

$result = $this->query($duplicateRecipientsQuery, true);

if ($result->num_rows > 0) {
    
    $toDeleteRecipients = [];
    
    while ($row = $result->fetch_assoc()) {
        
        $mailId = (int)$row['mail_id'];
        $email  = $row['customers_email_address'];
        
        if (array_key_exists($email, $toDeleteRecipients) === false) {
            
            // only create array and not adding $mailId to list
            // to ensure that the first ever created entry remains
            $toDeleteRecipients[$email] = [];
            continue;
        }
    
        $toDeleteRecipients[$email][] = $mailId;
    }
    
    $toDeleteEntryCount = array_sum(array_map('count', $toDeleteRecipients));
    
    if ($toDeleteEntryCount >= 1) {
    
        $toDeleteRecipients   = array_values($toDeleteRecipients);
        $toDeleteRecipients   = implode(',', array_merge(...$toDeleteRecipients));
        $deleteDuplicateQuery = <<<SQL
            DELETE FROM `newsletter_recipients` WHERE `mail_id` IN ($toDeleteRecipients)
        SQL;
    
        $t_success &= $this->query($deleteDuplicateQuery, true);
    }
}
